package fr.esisar.picaso.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import fr.esisar.picaso.entites.Livre;
import fr.esisar.picaso.moteur.Connexion;

/**
 * 
 * @author PALANDRI Thomas ©2012
 *
 */
public class RequetesStatistiques {

	public int nombreCommandes() throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT COUNT(idCommande) FROM U0.Commande";
		ResultSet rs = st.executeQuery(requete);
		
		int m = 0;
		while(rs.next()) {
			m = rs.getInt("Count(idCommande)");
		}
		
		return m;
	}
	
	public String meilleurClient() throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete ="SELECT idClient FROM u0.Commande " +
				"GROUP BY idClient " +
				"HAVING SUM(prixTotal)= (" +
				"SELECT MAX(SUM(prixTotal)) " +
				"FROM (SELECT idClient, SUM(prixTotal) FROM u0.Commande " +
				"GROUP BY idClient)" +
				")";
		ResultSet rs = st.executeQuery(requete);
		
		int idClient = 0;
		while(rs.next()) {
			idClient = rs.getInt("idClient");
		}
		
		RequetesClientAuthentifie r = new RequetesClientAuthentifie();
		String email = r.getEmail(idClient);
		
		return email;
	}
	
	
	public float prixMoyenCommandes() throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete ="SELECT AVG(prixTotal) as moyenne FROM u0.Commande";
		ResultSet rs = st.executeQuery(requete);
		
		float moyenne = 0;
		while(rs.next()) {
			moyenne = rs.getFloat("moyenne");
		}
		
		return moyenne;
	}
	
	public float recettes() throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete ="SELECT SUM(prixTotal) as recettes FROM u0.Commande";
		ResultSet rs = st.executeQuery(requete);
		
		float recettes = 0;
		while(rs.next()) {
			recettes = rs.getFloat("recettes");
		}
		
		return recettes;
	}
	
	
	public ArrayList<Livre> livresCommandesParTous() throws SQLException{
		ArrayList<Livre> res = new ArrayList<Livre>();
		
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete ="SELECT idAlbum from u0.Compose as T1 " +
				"WHERE NOT EXISTS( SELECT idClient FROM u0.ClientAuthentifie " +
				"WHERE NOT EXISTS ( SELECT T2.idClient " +
				"FROM u0.ClientAuthentifie as T2 " +
				"WHERE T1.idClient = T2.idClient))";
		System.out.println(requete);
		ResultSet rs = st.executeQuery(requete);
		
		
		while(rs.next()) {
			int idAlbum = rs.getInt("idAlbum");
			RequetesLivre r = new RequetesLivre();
			res.add(r.getLivre(idAlbum));
		}
		
		return res;
	}
	
}
